package com.riguz.forks.framework.db;

import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.riguz.forks.framework.Framework;
import com.riguz.forks.framework.db.dbutils.AffectedRowsHandler;
import com.riguz.forks.framework.db.dbutils.MappedBeanProcessor;

public final class Db {
    private static Logger logger = LoggerFactory.getLogger(Db.class);

    private Db() {

    }

    public static Long insert(String poolName, String sql, Object... params) {
        DataSource pool = Framework.getPoolManager().getPool(poolName);
        try {
            ResultSetHandler<Long> rsh = new AffectedRowsHandler();
            return new QueryRunner(pool).insert(sql, rsh, params);
        } catch (SQLException e) {
            handleSqlException(e, poolName, sql);
        }
        return null;
    }

    public static Long insert(String sql, Object... params) {
        return insert(PoolManager.DEFAULT_POOL_NAME, sql, params);
    }

    public static int execute(String poolName, String sql, Object... params) {
        DataSource pool = Framework.getPoolManager().getPool();
        try {
            return new QueryRunner(pool).update(sql, params);
        } catch (SQLException e) {
            handleSqlException(e, poolName, sql);
        }
        return -1;
    }

    public static int execute(String sql, Object... params) {
        return execute(PoolManager.DEFAULT_POOL_NAME, sql, params);
    }

    public static <T> T queryScalar(String poolName, Class<T> resultType, String sql, Object... params) {
        DataSource pool = Framework.getPoolManager().getPool();
        try {
            ScalarHandler<T> resultSetHandler = new ScalarHandler<T>();
            return new QueryRunner(pool).query(sql, resultSetHandler, params);
        } catch (SQLException e) {
            handleSqlException(e, poolName, sql);
        }
        return null;
    }

    public static <T> T queryScalar(Class<T> resultType, String sql, Object... params) {
        return queryScalar(PoolManager.DEFAULT_POOL_NAME, resultType, sql, params);
    }

    public static <T> T queryBean(String poolName, Class<T> beanType, String sql, Object... params) {
        DataSource pool = Framework.getPoolManager().getPool();
        try {
            BeanHandler<T> resultSetHandler = new BeanHandler<T>(beanType, new BasicRowProcessor(new MappedBeanProcessor<T>(beanType)));
            return new QueryRunner(pool).query(sql, resultSetHandler, params);
        } catch (SQLException e) {
            handleSqlException(e, poolName, sql);
        }
        return null;
    }

    public static <T> T queryBean(Class<T> beanType, String sql, Object... params) {
        return queryBean(PoolManager.DEFAULT_POOL_NAME, beanType, sql, params);
    }

    public static <T> List<T> queryBeanList(String poolName, Class<T> beanType, String sql, Object... params) {
        DataSource pool = Framework.getPoolManager().getPool();
        try {
            BeanListHandler<T> resultSetHandler = new BeanListHandler<T>(beanType, new BasicRowProcessor(new MappedBeanProcessor<T>(beanType)));
            return new QueryRunner(pool).query(sql, resultSetHandler, params);
        } catch (SQLException e) {
            handleSqlException(e, poolName, sql);
        }
        return null;
    }

    public static <T> List<T> queryBeanList(Class<T> beanType, String sql, Object... params) {
        return queryBeanList(PoolManager.DEFAULT_POOL_NAME, beanType, sql, params);
    }

    public static <T> List<T> queryColumn(String poolName, Class<T> columnType, String columnName, String sql, Object... params) {
        DataSource pool = Framework.getPoolManager().getPool();
        try {
            ColumnListHandler<T> resultSetHandler = new ColumnListHandler<T>(columnName);
            return new QueryRunner(pool).query(sql, resultSetHandler, params);
        } catch (SQLException e) {
            handleSqlException(e, poolName, sql);
        }

        return null;
    }

    public static <T> List<T> queryColumn(Class<T> columnType, String columnName, String sql, Object... params) {
        return queryColumn(PoolManager.DEFAULT_POOL_NAME, columnType, columnName, sql, params);
    }

    public static List<Object[]> queryObjectArrays(String poolName, String sql, Object... params) {
        DataSource pool = Framework.getPoolManager().getPool();
        try {
            ArrayListHandler resultSetHandler = new ArrayListHandler();
            return new QueryRunner(pool).query(sql, resultSetHandler, params);
        } catch (SQLException e) {
            handleSqlException(e, poolName, sql);
        }

        return null;
    }

    public static List<Object[]> queryObjectArrays(String sql, Object... params) {
        return queryObjectArrays(PoolManager.DEFAULT_POOL_NAME, sql, params);
    }

    public static int[] executeBatch(String poolName, String sql, Object[][] params) {
        DataSource pool = Framework.getPoolManager().getPool();
        try {
            return new QueryRunner(pool).batch(sql, params);
        } catch (SQLException e) {
            handleSqlException(e, poolName, sql);
        }

        return null;
    }
    
    public static int[] executeBatch(String sql, Object[][] params) {
        return executeBatch(PoolManager.DEFAULT_POOL_NAME, sql, params);
    }

    private static void handleSqlException(SQLException e, String pool, String sql) {
        DbException ex = new DbException(e, pool, sql);
        throw ex;
    }
}
